# -*- coding: utf-8 -*-
"""
Created on Thu Oct 14 11:16:43 2021

@author: liuqm
"""

import math 
import openpyxl

#根据经纬度计算两个点之间的距离，单位为米
def distance(lon1,lat1,lon2,lat2):
    radLat1 = math.radians(lat1)
    radLat2 = math.radians(lat2)
    a = radLat1 - radLat2
    b = math.radians(lon1)-math.radians(lon2)
    s = 2 * math.asin(math.sqrt(math.pow(math.sin(a/2), 2)+math.cos(radLat1)*math.cos(radLat2)*math.pow(math.sin(b/2), 2)))
    s = s * 6378.137*1000
    return s


fileName = r'input.xlsx'

book = openpyxl.load_workbook(fileName)
sheet = book['Sheet1']
sheet.cell(row=1,column=4).value = 'repeat'
dist_min = 10 #设置最小距离10米，小于最小距离的算作重复站点
maxrows = sheet.max_row

count = 0

for i in range(2,maxrows+1):
    name = sheet.cell(row=i,column=1).value
    lon = float(sheet.cell(row=i,column=2).value)
    lat = float(sheet.cell(row=i,column=3).value)
    print("正在计算第",i,'/',maxrows,'行:',name)
    for j in range(i+1,maxrows+1):
        name2 = sheet.cell(row=j,column=1).value
        lon2 = float(sheet.cell(row=j,column=2).value)
        lat2 = float(sheet.cell(row=j,column=3).value)
        d = distance(lon, lat, lon2, lat2)
        if d < dist_min :
            sheet.cell(row=j,column=4).value = 1
            count +=1

book.save('output.xlsx')
print("已删除距离小于10米的站点数： ",count)
        